//-----------------------------------------------------------------------
// <copyright file="IDSSortDAO.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------
	
using System;
using System.Data;
using MIMS.WRS.Entities;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MIMS.WRS.Data
{	
	public class ShoppingListDAO 
	{
		public ShoppingListDAO()
		{
		}

		#region Common methods
		public virtual ShoppingList CreateShoppingListFromReader(IDataReader reader)
		{
			ShoppingList item = new ShoppingList();
			try
			{
				if (!reader.IsDBNull(reader.GetOrdinal("Id"))) item.Id = (int)reader["Id"];
				if (!reader.IsDBNull(reader.GetOrdinal("IDSSort"))) item.IDSSort = (int)reader["IDSSort"];
				if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) item.ProductCode = (string)reader["ProductCode"];
				if (!reader.IsDBNull(reader.GetOrdinal("Description"))) item.Description = (string)reader["Description"];
				if (!reader.IsDBNull(reader.GetOrdinal("Qty"))) item.Qty = (int)reader["Qty"];
				if (!reader.IsDBNull(reader.GetOrdinal("UnitSetting"))) item.UnitSetting = (string)reader["UnitSetting"];
				if (!reader.IsDBNull(reader.GetOrdinal("UnitPrice"))) item.UnitPrice = (decimal)reader["UnitPrice"];
				if (!reader.IsDBNull(reader.GetOrdinal("TotalPrice"))) item.TotalPrice = (decimal)reader["TotalPrice"];
				if (!reader.IsDBNull(reader.GetOrdinal("PriorityIndex"))) item.PriorityIndex = (int)reader["PriorityIndex"];
				if (!reader.IsDBNull(reader.GetOrdinal("Notes"))) item.Notes = (string)reader["Notes"];
				if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderId"))) item.BulkOrderId = (int)reader["BulkOrderId"];
				if (!reader.IsDBNull(reader.GetOrdinal("UnitSettingID"))) item.UnitSettingID = (int)reader["UnitSettingID"];
				if (!reader.IsDBNull(reader.GetOrdinal("UnitWeight"))) item.UnitWeight = (decimal)reader["UnitWeight"];
				if (!reader.IsDBNull(reader.GetOrdinal("MinQtyVolume"))) item.MinQtyVolume = (decimal)reader["MinQtyVolume"];
				if (!reader.IsDBNull(reader.GetOrdinal("TerritoryID"))) item.TerritoryID = (string)reader["TerritoryID"];
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateShoppingListFromReaderException, ex);
			}
			return item;
		}
		#endregion
        
		#region CreateShoppingList methods
            
		public virtual void CreateShoppingList(ShoppingList shoppingList)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListInsert");
                
				dbCommandWrapper.AddInParameter("@IDSSort", DbType.Int32, shoppingList.IDSSort);
				dbCommandWrapper.AddInParameter("@ProductCode", DbType.String, shoppingList.ProductCode);
				dbCommandWrapper.AddInParameter("@Description", DbType.String, shoppingList.Description);
				dbCommandWrapper.AddInParameter("@Qty", DbType.Int32, shoppingList.Qty);
				dbCommandWrapper.AddInParameter("@UnitSetting", DbType.String, shoppingList.UnitSetting);
				dbCommandWrapper.AddInParameter("@UnitPrice", DbType.Decimal, shoppingList.UnitPrice);
				dbCommandWrapper.AddInParameter("@TotalPrice", DbType.Decimal,shoppingList.TotalPrice);
				dbCommandWrapper.AddInParameter("@PriorityIndex", DbType.Int32, shoppingList.PriorityIndex);
				dbCommandWrapper.AddInParameter("@Notes", DbType.String, shoppingList.Notes);
				dbCommandWrapper.AddInParameter("@BulkOrderId", DbType.Int32, shoppingList.BulkOrderId);
				dbCommandWrapper.AddInParameter("@UnitSettingID", DbType.Int32, shoppingList.UnitSettingID);
				dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Decimal, shoppingList.UnitWeight);
				dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Decimal, shoppingList.MinQtyVolume);
				dbCommandWrapper.AddInParameter("@TerritoryID", DbType.String, shoppingList.TerritoryID);
				dbCommandWrapper.AddOutParameter("@Id", DbType.Int32, 0);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
				shoppingList.Id = (int)dbCommandWrapper.GetParameterValue("@Id");
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateShoppingListException, ex);
			}
		}

		public virtual void DeleteFullShoppingOrderById(int shoppingOrderId)
		{
			try
			{
					Database database = DatabaseFactory.CreateDatabase();

					DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spAllShoppingOrderDeleteById");
					dbCommandWrapper.AddInParameter("@ShoppingOrderId", DbType.Int32, shoppingOrderId);
					DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException("DataAccessDeleteFullShoppingOrderByIdException", ex);
			}
		}

		/// <summary>
		/// Add new one list for new con point
		/// </summary>
		/// <param name="shoppingItemsList"></param>
		/// <param name="shoppingOrder"></param>
		/// <param name="shoppingUserId"></param>
		/// <param name="customerId"></param>
		public virtual void CreateShoppingOrderList(ShoppingListCollection shoppingItemsList, ShoppingOrder  shoppingOrder, int shoppingUserId, int customerId)
		{
			try
			{
				using (TransactionScope transaction = new TransactionScope())
				{
					Database database = DatabaseFactory.CreateDatabase();
					//delete shoping items of this user if existed then add new ones
					DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListDeleteByShoppingUserIdAndConPoint");
					dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingUserId);
					dbCommandWrapper.AddInParameter("@ConPointId", DbType.Int32, shoppingOrder.ConPointID);
					DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);

					//Create Order
					ShoppingOrderDAO shoppingOrderDAO = new ShoppingOrderDAO();
					shoppingOrderDAO.CreateShoppingOrder(shoppingOrder);

					foreach (ShoppingList shoppingList in shoppingItemsList)
					{
						dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListInsert");
						dbCommandWrapper.AddInParameter("@IDSSort", DbType.Int32, shoppingList.IDSSort);
						dbCommandWrapper.AddInParameter("@ProductCode", DbType.String, shoppingList.ProductCode);
						dbCommandWrapper.AddInParameter("@Description", DbType.String, shoppingList.Description);
						dbCommandWrapper.AddInParameter("@Qty", DbType.Int32, shoppingList.Qty);
						dbCommandWrapper.AddInParameter("@UnitSetting", DbType.String, shoppingList.UnitSetting);
						dbCommandWrapper.AddInParameter("@UnitPrice", DbType.Decimal, shoppingList.UnitPrice);
						dbCommandWrapper.AddInParameter("@TotalPrice", DbType.Decimal,shoppingList.TotalPrice);
						dbCommandWrapper.AddInParameter("@PriorityIndex", DbType.Int32, shoppingList.PriorityIndex);
						dbCommandWrapper.AddInParameter("@Notes", DbType.String, shoppingList.Notes);
						dbCommandWrapper.AddInParameter("@BulkOrderId", DbType.Int32, shoppingList.BulkOrderId);
						dbCommandWrapper.AddInParameter("@UnitSettingID", DbType.Int32, shoppingList.UnitSettingID);
						dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Decimal, shoppingList.UnitWeight);
						dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Decimal, shoppingList.MinQtyVolume);
						dbCommandWrapper.AddInParameter("@TerritoryID", DbType.String, shoppingList.TerritoryID);
						dbCommandWrapper.AddOutParameter("@Id", DbType.Int32, 0);
						//Create shopping item
						DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
						shoppingList.Id = (int)dbCommandWrapper.GetParameterValue("@Id");

						//Create a link between shopping item with order
						dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingLinkCreate");
						dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingOrder.ShoppingOrderId);
						dbCommandWrapper.AddInParameter("@Id", DbType.Int32, shoppingList.Id);
						dbCommandWrapper.AddInParameter("@CustomerID", DbType.Int32,((customerId==0 || customerId ==Nulls.NullInteger)? DBNull.Value:(object)customerId));
						dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, DateTime.Now);
						DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
					}
					transaction.Complete();
				}
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateShoppingListException, ex);
			}
		}


		public virtual void CreateShoppingOrderListToExistedList(ShoppingListCollection shoppingItemsList, ShoppingOrder  shoppingOrder, int shoppingUserId, int customerId)
		{
			try
			{
				using (TransactionScope transaction = new TransactionScope())
				{
					Database database = DatabaseFactory.CreateDatabase();

					//delete shoping items of this user if existed then add new ones ( already merge the existing lisy with seleciton
					DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListDeleteByShoppingUserIdAndConPoint");
					dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingUserId);
					dbCommandWrapper.AddInParameter("@@ConPointId", DbType.Int32, shoppingOrder.ConPointID);
					DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);

					//Create Order
					ShoppingOrderDAO shoppingOrderDAO = new ShoppingOrderDAO();
					shoppingOrderDAO.UpdateShoppingOrder(shoppingOrder);

					foreach (ShoppingList shoppingList in shoppingItemsList)
					{
						dbCommandWrapper =  database.GetStoredProcCommandWrapper("spShoppingListInsert");
						dbCommandWrapper.AddInParameter("@IDSSort", DbType.Int32, shoppingList.IDSSort);
						dbCommandWrapper.AddInParameter("@ProductCode", DbType.String, shoppingList.ProductCode);
						dbCommandWrapper.AddInParameter("@Description", DbType.String, shoppingList.Description);
						dbCommandWrapper.AddInParameter("@Qty", DbType.Int32, shoppingList.Qty);
						dbCommandWrapper.AddInParameter("@UnitSetting", DbType.String, shoppingList.UnitSetting);
						dbCommandWrapper.AddInParameter("@UnitPrice", DbType.Decimal, shoppingList.UnitPrice);
						dbCommandWrapper.AddInParameter("@TotalPrice", DbType.Decimal,shoppingList.TotalPrice);
						dbCommandWrapper.AddInParameter("@PriorityIndex", DbType.Int32, shoppingList.PriorityIndex);
						dbCommandWrapper.AddInParameter("@Notes", DbType.String, shoppingList.Notes);
						dbCommandWrapper.AddInParameter("@BulkOrderId", DbType.Int32, shoppingList.BulkOrderId);
						dbCommandWrapper.AddInParameter("@UnitSettingID", DbType.Int32, shoppingList.UnitSettingID);
						dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Decimal, shoppingList.UnitWeight);
						dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Decimal, shoppingList.MinQtyVolume);
						dbCommandWrapper.AddInParameter("@TerritoryID", DbType.String, shoppingList.TerritoryID);
						dbCommandWrapper.AddOutParameter("@Id", DbType.Int32, 0);
						//Create shopping item
						DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
						shoppingList.Id = (int)dbCommandWrapper.GetParameterValue("@Id");

						//Create a link between shopping item with order
						dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingLinkCreate");
						dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingOrder.ShoppingOrderId);
						dbCommandWrapper.AddInParameter("@Id", DbType.Int32, shoppingList.Id);
						dbCommandWrapper.AddInParameter("@CustomerID", DbType.Int32,((customerId==0 || customerId ==Nulls.NullInteger)? DBNull.Value:(object)customerId));
						dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, DateTime.Now);
						DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
					}
					transaction.Complete();
				}
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateShoppingListException, ex);
			}
		}


		public virtual void DeleteShoppingOrderList(int shoppingUserId, int conPointId)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				//delete shoping items of this user if existed then add new ones
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListDeleteByShoppingUserIdAndConPoint");
				dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingUserId);
				dbCommandWrapper.AddInParameter("@ConPointId", DbType.Int32, conPointId);
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException("SR.DeleteShoppingOrderListException", ex);
			}
		}

		#endregion

		#region UpdateShoppingList methods
        
		public virtual void UpdateShoppingList(ShoppingList shoppingList)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListUpdate");            
                
				dbCommandWrapper.AddInParameter("@Id", DbType.Int32, shoppingList.Id);
				dbCommandWrapper.AddInParameter("@IDSSort", DbType.Int32, shoppingList.IDSSort);
				dbCommandWrapper.AddInParameter("@ProductCode", DbType.String, shoppingList.ProductCode);
				dbCommandWrapper.AddInParameter("@Description", DbType.String, shoppingList.Description);
				dbCommandWrapper.AddInParameter("@Qty", DbType.Int32, shoppingList.Qty);
				dbCommandWrapper.AddInParameter("@UnitSetting", DbType.String, shoppingList.UnitSetting);
				dbCommandWrapper.AddInParameter("@UnitPrice", DbType.Decimal, shoppingList.UnitPrice);
				dbCommandWrapper.AddInParameter("@TotalPrice", DbType.Decimal, shoppingList.TotalPrice);
				dbCommandWrapper.AddInParameter("@PriorityIndex", DbType.Int32, shoppingList.PriorityIndex);
				dbCommandWrapper.AddInParameter("@Notes", DbType.String, shoppingList.Notes);
				dbCommandWrapper.AddInParameter("@BulkOrderId", DbType.Int32, shoppingList.BulkOrderId);
				dbCommandWrapper.AddInParameter("@UnitSettingID", DbType.Int32, shoppingList.UnitSettingID);
				dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Decimal, shoppingList.UnitWeight);
				dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Decimal, shoppingList.MinQtyVolume);
				dbCommandWrapper.AddInParameter("@TerritoryID", DbType.String, shoppingList.TerritoryID);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessUpdateShoppingListException, ex);
			}
		}
        
		#endregion

		#region DeleteShoppingList methods
		public virtual void DeleteShoppingList(int id)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListDelete");
                
				dbCommandWrapper.AddInParameter("@Id", DbType.Int32, id);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessDeleteShoppingListException, ex);
			}
		}

		#endregion

		#region GetShoppingList methods
        
		public virtual ShoppingList GetShoppingList(int id)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListGet");
                
				dbCommandWrapper.AddInParameter("@Id", DbType.Int32, id);
                
				ShoppingList shoppingList =  null;
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					if (reader.Read())
					{
						shoppingList = CreateShoppingListFromReader(reader);
						reader.Close();
					}
				}
				return shoppingList;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShoppingListException, ex);
			}
		}

		public virtual ShoppingListCollection GetShoppingListByShoppingUserIdAndConPoint(int shoppingUserId, int conPointID)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListGetByShoppingUserIdAndConPoint");
                
				dbCommandWrapper.AddInParameter("@ShoppingUserId", DbType.Int32, shoppingUserId);
				dbCommandWrapper.AddInParameter("@ConPointId", DbType.Int32, conPointID);
                
				ShoppingListCollection shoppingList = new ShoppingListCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShoppingList shoppingItem = CreateShoppingListFromReader(reader);
						shoppingList.Add(shoppingItem);
					}
					reader.Close();
				}
				return shoppingList;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException("DataAccessGetShoppingListByShoppingUserIdException", ex);
			}
		}
        

		public virtual ShoppingListCollection GetShoppingListByShoppingOrderId(int shoppingOrderId)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListGetByShoppingOrderId");
                
				dbCommandWrapper.AddInParameter("@ShoppingOrderId", DbType.Int32, shoppingOrderId);
                
				ShoppingListCollection shoppingList = new ShoppingListCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShoppingList shoppingItem = CreateShoppingListFromReader(reader);
						shoppingList.Add(shoppingItem);
					}
					reader.Close();
				}
				return shoppingList;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException("GetShoppingListByShoppingOrderId", ex);
			}
		}

		public virtual IDataReader GetShoppingListByShoppingOrderIdReport(int shoppingOrderId)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShoppingListGetByShoppingOrderId");
                
				dbCommandWrapper.AddInParameter("@ShoppingOrderId", DbType.Int32, shoppingOrderId);
                
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException("GetShoppingListByShoppingOrderIdReport", ex);
			}
		}
		#endregion

	}
}
